<?php

define("SUBMITTED_STATE", 3);
require 'db_config.php';

class Service {


	private $my_user_id;
	private $db_connection;
	
	function __construct() {
		date_default_timezone_set('Europe/Warsaw');
		// Nawiazanie polaczenia z baza danych:
		$this->db_connection = new mysqli(Config::$db_host, Config::$db_user, Config::$db_password, Config::$db_name, Config::$db_port);
		if ($this->db_connection->connect_errno) {
			die ('Error: Connection to database failed.');
		}
		$this->db_connection->set_charset("utf8");
	}
	
	function getProductData() {
		$result = array("exitCode" => 0);
		if ($this->checkCredentials()) {
			$result["exitCode"] = 1;
			$queryResult = $this->db_connection->query("SELECT p1.*, ifnull(p3.price,p2.price) as price FROM `products` p1 JOIN `prices` p2 ON p1.id = p2.product_id AND p2.company_id IS NULL LEFT JOIN `prices` p3 ON p1.id = p3.product_id AND p3.company_id = (SELECT `company_id` FROM `company_session` WHERE `session_id` = \"".session_id()."\")");
			$rows = array();
			while($r = $queryResult->fetch_assoc()) {
				$rows[] = $r;
			}
			$result["result"] = $rows;
			$queryResult->close();
		}
		return $result;
	}
	
	function getDepartments() {
		$result = array("exitCode" => 0);
		if ($this->checkCredentials()) {
			$result["exitCode"] = 1;
			$queryResult = $this->db_connection->query("SELECT * FROM `departments` WHERE company_id = (SELECT `company_id` FROM `company_session` WHERE `session_id` = \"".session_id()."\")");
			$rows = array();
			while($r = $queryResult->fetch_assoc()) {
				$rows[] = $r;
			}
			$result["result"] = $rows;
			$queryResult->close();
		}
		return $result;
	}
	
	function saveOrder($orderArr) {
		$result = array("exitCode" => 0);
		if ($this->checkCredentials()) {
			$result["exitCode"] = 1;
			if ($orderArr["mDbId"] != 0) {
				$orderId = $orderArr["mDbId"];
				$result["orderId"] = $orderId;
				$this->db_connection->query("UPDATE `orders` SET `number` = \"".$orderArr["mNumber"]."\", `costUnit` = \"".$orderArr["mCostUnit"]."\", `date` = \"".$orderArr["mDate"]."\", `state` = \"".$orderArr["mState"]."\", `paymentMethod` = \"".$orderArr["mPaymentMethod"]."\" WHERE `id` = \"".$orderId."\"");
				
				$queryResult = $this->db_connection->query("SELECT `id`, `department_id`, `product_id` FROM `order_elements` WHERE `order_id` = \"".$orderId."\"");
				$removedDepIds = array();
				$updatedDepIds = array();
				while($r = $queryResult->fetch_assoc()) {
					if (isset($removedDepIds[$r["department_id"]])) // departament został usunięty
						continue;
					if (!isset($orderArr["mOrderDepartments"][$r["department_id"]])) { // departament jest w tabeli, ale nie ma go w danych od klienta
						$this->db_connection->query("DELETE FROM `order_department_data` WHERE `order_id` = \"".$orderId."\" AND `department_id` = \"".$r["department_id"]."\"");
						//Following line is commented out, as CASCADE DELETE should work
						//$this->db_connection->query("DELETE FROM `order_elements` WHERE `order_id` = \"".$orderId."\" AND `department_id` = \"".$r["department_id"]."\"");
						$removedDepIds[] = $r["department_id"];
					} else { // departament jest w tabeli i jest w danych od klienta
						$tmp = $orderArr["mOrderDepartments"][$r["department_id"]];
						if (!isset($updatedDepIds[$r["department_id"]])) {
							$this->db_connection->query("UPDATE `order_department_data` SET `order_receiver` = \"".$tmp["mOrderReceiver"]."\", `contact_phone` = \"".$tmp["mContactPhone"]."\", `date1` = \"".$tmp["mDate1"]."\", `date2` = \"".$tmp["mDate2"]."\", `hour` = \"".$tmp["mHour"]."\", `comments` = \"".$tmp["mComments"]."\" WHERE `order_id` = \"".$orderId."\" AND `department_id` = \"".$r["department_id"]."\"");
							$updatedDepIds[] = $r["department_id"];
						}
						$updated = false;
						foreach ($tmp["mOrderElements"] as $key => $val) {
							if ($val["mProductId"] == $r["product_id"]) {
								$this->db_connection->query("UPDATE `order_elements` SET `price` = \"".$val["mPrice"]."\", `quantity` = \"".$val["mQuantity"]."\", `reinvoice` = \"".$val["mReinvoice"]."\" WHERE `id` = \"".$r["id"]."\"");
								unset($orderArr["mOrderDepartments"][$r["department_id"]]["mOrderElements"][$key]);
								$updated = true;
								break;
							}
						}
						if (!$updated) // produkt jest w tabeli, ale nie ma go w danych od użytkownika
							$this->db_connection->query("DELETE FROM `order_elements` WHERE `id` = \"".$r["id"]."\"");
					}
				}
				$queryResult->close();
				foreach ($orderArr["mOrderDepartments"] as $k => $v) {
					$orderDepartmentId = 0;
					if (isset($updatedDepIds[$k])) {
						$queryResult = $this->db_connection->query("SELECT `id` FROM `order_department_data` WHERE `order_id` = \"".$orderId."\" AND `department_id` = \"".$k."\"");
						$r = $queryResult->fetch_assoc();
						$orderDepartmentId = $r["id"];
						$queryResult->close();
					} else {
						$queryResult = $this->db_connection->query("SELECT `id` FROM `order_department_data` WHERE `order_id` = \"".$orderId."\" AND `department_id` = \"".$k."\"");
						if ($this->db_connection->affected_rows == 0) {
							$this->db_connection->query("INSERT INTO `order_department_data` (`order_id`, `department_id`, `order_receiver`, `contact_phone`, `date1`, `date2`, `hour`, `comments`) VALUES (\"".$orderId."\", \"".$k."\", \"".$v["mOrderReceiver"]."\", \"".$v["mContactPhone"]."\", \"".$v["mDate1"]."\", \"".$v["mDate2"]."\", \"".$v["mHour"]."\", \"".$v["mComments"]."\")");
							$orderDepartmentId = $this->db_connection->insert_id;
						} else {
							$r = $queryResult->fetch_assoc();
							$orderDepartmentId = $r["id"];
							$this->db_connection->query("UPDATE `order_department_data` SET `order_receiver` = \"".$v["mOrderReceiver"]."\", `contact_phone` = \"".$v["mContactPhone"]."\", `date1` = \"".$v["mDate1"]."\", `date2` = \"".$v["mDate2"]."\", `hour` = \"".$v["mHour"]."\", `comments` = \"".$v["mComments"]."\" WHERE `order_id` = \"".$orderId."\" AND `department_id` = \"".$k."\"");
						}
						$queryResult->close();
					}
					foreach ($v["mOrderElements"] as $v2) {
						$this->db_connection->query("INSERT INTO `order_elements` (`order_id`, `department_id`, `order_department_id`, `product_id`, `price`, `quantity`, `reinvoice`) VALUES (\"".$orderId."\", \"".$k."\", \"".$orderDepartmentId."\", \"".$v2["mProductId"]."\", \"".$v2["mPrice"]."\", \"".$v2["mQuantity"]."\", \"".$v2["mReinvoice"]."\")");
					}
				}
			} else {
				$this->db_connection->query("INSERT INTO `orders` (`number`, `costUnit`, `date`, `state`, `paymentMethod`, `user_id`) VALUES (\"".$orderArr["mNumber"]."\", \"".$orderArr["mCostUnit"]."\", \"".$orderArr["mDate"]."\", \"".$orderArr["mState"]."\", \"".$orderArr["mPaymentMethod"]."\", \"".$this->my_user_id."\")");
				$orderId = $this->db_connection->insert_id;
				$result["orderId"] = $orderId;
				foreach ($orderArr["mOrderDepartments"] as $k => $v) {
					$this->db_connection->query("INSERT INTO `order_department_data` (`order_id`, `department_id`, `order_receiver`, `contact_phone`, `date1`, `date2`, `hour`, `comments`) VALUES (\"".$orderId."\", \"".$k."\", \"".$v["mOrderReceiver"]."\", \"".$v["mContactPhone"]."\", \"".$v["mDate1"]."\", \"".$v["mDate2"]."\", \"".$v["mHour"]."\", \"".$v["mComments"]."\")");
					$orderDepartmentId = $this->db_connection->insert_id;
					foreach ($v["mOrderElements"] as $v2) {
						$this->db_connection->query("INSERT INTO `order_elements` (`order_id`, `department_id`, `order_department_id`, `product_id`, `price`, `quantity`, `reinvoice`) VALUES (\"".$orderId."\", \"".$k."\", \"".$orderDepartmentId."\", \"".$v2["mProductId"]."\", \"".$v2["mPrice"]."\", \"".$v2["mQuantity"]."\", \"".$v2["mReinvoice"]."\")");
					}
				}
			}
		}
		return $result;
	}
	
	function getOrderList($onlySubmitted) {
		$result = array("exitCode" => 0, "result" => "");
		if ($this->checkCredentials()) {
			$result["exitCode"] = 1;
			if ($onlySubmitted) {
				$queryResult = $this->db_connection->query("SELECT * FROM `users` WHERE `id` = \"".$this->my_user_id."\"");
				$r = $queryResult->fetch_assoc();
				$companyId = $r["company_id"];
				$queryResult->close();
				$queryResult = $this->db_connection->query("SELECT o.*, u.`login` as user_login, null as approver_login FROM `orders` o LEFT JOIN `users` u ON u.`id` = o.`user_id` WHERE o.`state` = \"".SUBMITTED_STATE."\" AND o.`user_id` IN (SELECT `id` FROM `users` WHERE `company_id` = \"".$companyId."\")");
			} else
				$queryResult = $this->db_connection->query("SELECT o.*, null as user_login, u.`login` as approver_login FROM `orders` o LEFT JOIN `users` u ON u.`id` = o.`approver_id` WHERE o.`user_id` = \"".$this->my_user_id."\")");
			if ($this->db_connection->affected_rows == 0)
				return $result;
			$rows = array();
			while($r = $queryResult->fetch_assoc()) {
				$queryResult2 = $this->db_connection->query("SELECT `department_id` FROM `order_department_data` WHERE `order_id` = \"".$r["id"]."\"");
				$rows2 = array();
				while ($r2 = $queryResult2->fetch_assoc())
					$rows2[$r2["department_id"]] = array();
				$r["orderDepartments"] = $rows2;
				$rows[] = $r;
				$queryResult2->close();
			}
			$result["result"] = $rows;
			$queryResult->close();
		}
		return $result;
	}
	
	function getOrder($orderId) {
		$result = array("exitCode" => 0, "result" => "");
		if ($this->checkCredentials()) {
			$queryResult = $this->db_connection->query("SELECT o.*, u1.`login` as user_login, u2.`login` as approver_login FROM `orders` o LEFT JOIN `users` u1 ON u1.`id` = o.`user_id` LEFT JOIN `users` u2 ON u2.`id` = o.`approver_id` WHERE o.`id` = \"".$orderId."\"");
			if ($this->db_connection->affected_rows == 0)
				return $result;
			$result["exitCode"] = 1;
			$r = $queryResult->fetch_assoc();
			$queryResult2 = $this->db_connection->query("SELECT * FROM `order_department_data` WHERE `order_id` = \"".$orderId."\"");
			$rows = array();
			while ($r2 = $queryResult2->fetch_assoc()) {
				//Get order department elements
				$queryResult3 = $this->db_connection->query("SELECT * FROM `order_elements` WHERE `order_department_id` = \"".$r2["id"]."\"");
				$rows2 = array();
				while ($r3 = $queryResult3->fetch_assoc()) {
					$rows2[] = array(
						"product_id" => $r3["product_id"],
						"price" => $r3["price"],
						"quantity" => $r3["quantity"],
						"reinvoice" => $r3["reinvoice"]);
				}
				//Save it
				$rows[$r2["department_id"]] = array(
					"orderReceiver" => $r2["order_receiver"],
					"contactPhone" => $r2["contact_phone"],
					"date1" => $r2["date1"],
					"date2" => $r2["date2"],
					"hour" => $r2["hour"],
					"comments" => $r2["comments"],
					"orderElements" => $rows2);
			}
			$r["orderDepartments"] = $rows;
			$result["result"] = $r;
			$queryResult2->close();
			$queryResult->close();
		}
		return $result;
	}
	
	function login($username, $password) {
		$result = array("exitCode" => 0,
						"message" => "",
						"userId" => "",
						"superUser" => "",
						"name" => "",
						"address1" => "",
						"address2" => "",
						"contact" => "",
						"image" => 0);
		if ($this->db_connection) {
			// Przefiltrowanie parametrow:
			$username = $this->db_connection->real_escape_string($username);
			$password = $this->db_connection->real_escape_string($password);
			
			// Sprawdzenie poprawności nazwy użytkownika i hasła
			$queryResult = $this->db_connection->query("SELECT u.`id`, u.`password`, u.`super_user`, u.`salt`, c.`name`, c.`address1`, c.`address2`, c.`contact`, c.`id` as company_id FROM `users` u JOIN `companies` c ON c.`id` = u.`company_id` WHERE u.`login` = '".$username."'");
			if ($this->db_connection->affected_rows != 0) // Użytkownik istnieje
			{
				$row = $queryResult->fetch_assoc();
				if ($row['password'] == md5($password.$row['salt'])) {
					$result["exitCode"] = 1;
					$result["message"] = "OK";
					$result["userId"] = $row["id"];
					$result["superUser"] = $row["super_user"];
					$result["name"] = $row["name"];
					$result["address1"] = $row["address1"];
					$result["address2"] = $row["address2"];
					$result["contact"] = $row["contact"];
					$result["image"] = $row["company_id"];
					$sid = session_id();
					$this->db_connection->query("SELECT * FROM `sessions` WHERE `session_id` = \"".$sid."\" AND `user_id` = \"".$row["id"]."\"");
					if ($this->db_connection->affected_rows == 0) {
						$this->db_connection->query("DELETE FROM `sessions` WHERE `user_id` = \"".$row["id"]."\"");
						$queryResult2 = $this->db_connection->query("INSERT INTO `sessions` (`session_id`, `user_id`, `ip_address`) VALUES (\"".$sid."\", \"".$row["id"]."\", \"".$_SERVER['REMOTE_ADDR']."\")");
						if (!$queryResult2) {
							$result["exitCode"] = 0;
							$result["message"] = $this->db_connection->error;
						}
					}
				} else
					$result["message"] = "Hasło niepoprawne.";
			} else
				$result["message"] = "Użytkownik nie istnieje.";
			$queryResult->close();
		}
		return $result;
	}
	
	function register($username, $password, $name, $address) {
		$result = array("exitCode" => 0);
		if ($this->db_connection) {
			
			// Przefiltrowanie parametrow:
			$username = $this->db_connection->real_escape_string($username);
			$password = $this->db_connection->real_escape_string($password);
			$name = $this->db_connection->real_escape_string($name);
			$address = $this->db_connection->real_escape_string($address);
				
			// Wygenerowanie hashu hasła
			$salt = sha1(md5($password));
			$passHashed = md5($password.$salt);
			try 
			{
				$queryResult = $this->db_connection->query("INSERT INTO `users` (`login`, `password`, `salt`, `name`, `address`) values (\"".$username."\", \"".$passHashed."\", \"".$salt."\", \"".$name."\", \"".$address."\");");
				if (!$queryResult) 
					return $result;
				if ($this->db_connection->affected_rows != 0) // Konto zostalo utworzone
					$result["exitCode"] = 1;
			} 
			catch (Exception $e) 
			{
				$result["exitCode"] = 2;
				$result["message"] = $e->getMessage();
			}
		}
		return $result;
	}
	
	function checkCredentials() {
		if ($this->db_connection) {
			$resultQuery = $this->db_connection->query("SELECT * FROM `sessions` WHERE `session_id` = \"".session_id()."\" AND `ip_address` = \"".$_SERVER['REMOTE_ADDR']."\"");
			$result = $this->db_connection->affected_rows != 0;
			if ($result) {
				$r = $resultQuery->fetch_assoc();
				$this->my_user_id = $r["user_id"];
			}
			return $result;
		}
		return false;
	}
	
	function __destruct() {
		$this->db_connection->close();
	}
}

?>
